import os
import pandas
from openpyxl import Workbook
from openpyxl import load_workbook
import win32com.client as win32
from openpyxl.styles import Font,Alignment, Border, Side, PatternFill,GradientFill
from openpyxl.utils import get_column_letter, column_index_from_string

# 1、获取一个工作簿
wb = Workbook()  # 创建新的工作簿
wb1 = load_workbook('AzDO20211012.xlsx')  # 加载已存在的工作簿
sheet_names1 = wb.sheetnames  # 获取工作表名称
print(sheet_names1)
sheet_names2 = wb1.sheetnames
print(sheet_names2)

# 2、获取当前工作表sheet
ws = wb.active
print(ws)

# 3、创建工作表
ws = wb.create_sheet('test1')
ws = wb.create_sheet('test2', 0)
ws0 = wb.create_sheet(title='test3', index=3)
ws = wb.create_sheet()  # 什么参数都不写的话，默认插入到最后一个位置且名字为sheet,sheet1...按照顺序排列
sheet_names1 = wb.sheetnames
print(sheet_names1)

# 修改工作表名
ws.title = 'active'
sheet_names1 = wb.sheetnames
print(sheet_names1)

# 选择工作表
ws11 = wb['active']
ws12 = wb.get_sheet_by_name('active')
print(ws11 is ws12)

# 删除sheet
wb.remove(ws0)  # 这里只能写worksheet对象，不能写sheetname
sheet_names1 = wb.sheetnames
print(sheet_names1)

# 复制sheet
wb.copy_worksheet(ws11)
print(wb.sheetnames)

# 当前工作表
ws1 = wb1.active
print(ws1)
# 修改sheet选项卡背景色，默认为白色，设置为RRGGBB模式
ws1.sheet_properties.tabColor = 'FFA500'
# 读取有效区域
print('最大列数为:', ws1.max_column)
print('最大行数为:', ws1.max_row)

# 插入行和列
ws1.insert_rows(1)  # 在第一行插入一行
ws1.insert_cols(5, 2)  # 从第五列开始插入两列

# 删除行和列
ws1.delete_cols(5, 1)  # 从第五列（E列）开始，删除1列即（E:F）
ws1.delete_rows(1)   # 删除第一行

# 4、单元格操作
# 读取
c = ws1['A1']
e = ws1.cell(row=1, column=2)
h = ws1.cell(1, 4)
print(c, e, h)
print(c.value, e.value, h.value)
# 单元格列索引
print(c.column, h.col_idx)
# 单元格行索引
print(c.row)
# 单元格列名
print(c.column_letter)
# 单元格坐标
print(c.coordinate)
# 单元格数字类型：默认是n 数值，s 字符串，d 日期时间
print(c.data_type)
# 单元格编码格式，默认'utf-8'
print(c.encoding)
# 是否有样式：默认样式为Normal,默认样式，返回False
print(c.has_style)
# 单元格样式和样式id
print(c.style, c.style_id)

# 切片访问单元格区域
cell_range = ws1['A1:C3']
print("切片访问多个单元格：",cell_range)
import datetime
# 单元格赋值
ws1['E1'] = 'Daniel'
print(ws1.cell(1, 5).value)
ws1.cell(2, 5).value = 'Daniel-2'
print(ws1.cell(2, 5).value)
ws1.cell(3, 5).value = 101
# 插入公式
ws1["A5"] = "=sum(A1:A3)"
# 插入日期时间，设置格式
ws1['A6'] = datetime.datetime(2025, 7, 21)
ws1['A6'].number_format = 'yyyy-mm-dd h:mm:ss'
# 增加一行
ws1.append([1, 2, 3, 'Daniel'])

# 单元格遍历：ws.values 返回的是生成器，是将一行数据作为一个元组单元组成的，是由值组成的，ws.values 获取的内容是从 “A1” 到 “最大行最大列”
print('单元格遍历1：')
for i in ws1.values:
    print(i)
# ws.rows 是将一行单元格作为元组单元组成的生成器，与ws.values的区别是，rows返回的是由单元格组成的元组，values是由值组成的
print('单元格遍历2：')
for i in ws1.rows:
    for j in i:
        print(j, j.value)
# rows 和 iter_rows()的区别在于，iter_row()可以指定区域，rows是全部单元格
# columns、iter_cols()按列
print('单元格遍历3：')
for i in ws1.columns:
    print(i)

# 转pandas
df = pandas.DataFrame(ws1.values)
print(df)

# 合并单元格
ws1.merge_cells('E1:F1')
ws1.merge_cells('E3:E4')
# 已存在的合并的单元格
print(ws1.merged_cells)
# 拆分单元格
ws1.unmerge_cells('E1:F1')

# 设置单元格风格
# 设置字体风格
ws1['E1'].font = Font(name='Arial', size=12, bold=True, italic=False, color='FF0000')
# 设置对齐方式
# horizontal 水平对齐模式, vertical 垂直对齐模式, text_rotation 旋转角度, wrap_text 是否自动换行, shrink_to_fit 是否根据字符长度缩小以适应单元格, indent 缩进距离)
# 水平对齐：‘distributed’，‘justify’，‘center’，‘left’， ‘centerContinuous’，'right，‘general’
# 垂直对齐：‘bottom’，‘distributed’，‘justify’，‘center’，‘top
ws1['E1'].alignment = Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0)
# 设置边框
ws1['A6'].border = Border(left=Side(border_style='thin', color='000000'),right=Side(border_style='thin', color='000000'),top=Side(border_style='thin', color='000000'),bottom=Side(border_style='thin', color='000000'),diagonal=Side(border_style='thin', color='000000'),diagonalUp=True,diagonalDown=True,)
# 设置行列的宽度
ws1.column_dimensions['A'].width = 10
ws1.row_dimensions[2].height = 30
# 设置背景色
ws1['A7'].fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
ws1['C7'].fill = PatternFill("solid", fgColor="FFFF00")
# 设置渐变背景色
ws1['B7'].fill =GradientFill('linear',degree=0, left=0, right=0, top=0,bottom=0, stop=("000000","99ccff","FFFFFF"))
ws1['D7'].fill = GradientFill("linear", stop=("FFFFFF","99ccff","000000"))

# 字母列号与数字列号的转换
print("根据列的数字返回字母：",get_column_letter(1))
print("根据列的字母返回数字：",column_index_from_string("D"))
# 输出字体样式
print("输出E1字体样式：",ws1['E1'].font.name)

# 保存：save（必须要写文件名（绝对地址）默认 py 同级目录下，只支持 xlsx 格式）
wb.save('test.xlsx')
wb1.save('test1.xlsx')
# 关闭文件
wb.close()
wb1.close()

# # openpyxl只能操作xlsx文件，当我们遇到xls文件的时候就需要进行转化
# filename = r'F:\Desktop\Demo\AzDO20211012.xls'
# Excelapp = win32.gencache.EnsureDispatch('Excel.Application')
# workbook = Excelapp.Workbooks.Open(filename)
# # 转xlsx时: FileFormat=51,
# # 转xls时:  FileFormat=56,
# workbook.SaveAs(filename.replace('xls', 'xlsx'), FileFormat=51)
# workbook.Close()
# Excelapp.Application.Quit()
# # 删除源文件
# # os.remove(filename)
# # 如果想将xlsx的文件转换为xls的话，则可以使用以下的代码：
# # workbook.SaveAs(filename.replace('xlsx', 'xls'), FileFormat=56)

